# -*- coding: utf-8 -*-
# 如果你的程序（包括注释）中包含中文，请加上上面这一行，用于指明编码（utf-8）。
# #号开头的行都是注释。

# 如何编辑本程序：
#     用任何文本编辑器均可编辑。

# 如何运行本程序：
#     在终端中，运行：python p701_sqlite3.py
#
# 参考链接：https://www.python-course.eu/sql_python.php
#

# 操作数据库的基本流程：
# （1）调用connect()方法打开数据库连接，该方法返回数据库连接对象。
# （2）通过数据库连接对象打开游标（cursor）。
# （3）使用游标执行SQL语句。如果执行的是查询语句，则后续处理查询结果。
# （4）关闭游标。
# （5）关闭数据库连接。

# sqlite3是标准Python模块之一。
import sqlite3

# sqlite3直接操作数据库文件，不需要服务器进程。
# （1）sqlite3.connect连接数据库，如果指定文件不存在，就会创建一个数据库文件
# （相当于 create database ...）。
connection = sqlite3.connect('tmp.sqlite3')

# （2）获取cursor对象，用于数据库操作。
cursor = connection.cursor()

# （3）使用SQL语句操作数据库。

# 用于创建数据表的SQL语句：
create_sql = """
CREATE TABLE employee ( 
staff_number INTEGER PRIMARY KEY, 
fname VARCHAR(20), 
lname VARCHAR(30), 
gender CHAR(1), 
joining DATE,
birth_date DATE)"""

# 查询相应的表是否存在。如果存在的话，以下查询应该返回一条记录。
cursor.execute("SELECT COUNT(*) FROM sqlite_master WHERE type='table' AND name='employee'")

# 使用cursor.fetchone()方法或者cursor.fetchall()方法获取查询结果。
# 前者返回一条记录（可以多次调用以获取查询结果中的所有记录），tuple形式。
# 后者返回所有记录，list形式，list的每一个元素是一个tuple，表示一条记录。

# 如果employee表存在，则fetchone返回(1,)
# 如果employee表不存在，则fetchone返回(0,)
(i, ) = cursor.fetchone()  # 
if i == 0:
	# 表不存在，先创建这个表。
	cursor.execute(create_sql)
else:
	# 表已经存在，以下语句将旧的同名表删除。
    cursor.execute('DROP TABLE employee')
    # 再创建表。
    cursor.execute(create_sql)

# commit使得以上操作生效：
connection.commit()


# 注：可用SQLite相关的数据库管理软件来手工操作数据库，比如：
#     http://www.sqliteexpert.com/download.html


# 插入两条记录：

insert_sql = """INSERT INTO employee (staff_number, fname, lname, gender, birth_date)
    VALUES (NULL, "William", "Shakespeare", "m", "1961-10-25")"""
cursor.execute(insert_sql)
print("%d rows inserted." % cursor.rowcount)

insert_sql = """INSERT INTO employee (staff_number, fname, lname, gender, birth_date)
    VALUES (NULL, "Frank", "Schiller", "m", "1955-08-17")"""
cursor.execute(insert_sql)
print("%d rows inserted." % cursor.rowcount)

# 注：staff_number是主键，如果指定为NULL，SQLite会自动设置为递增的整数。

# 也可以使用带参数的SQL语句，参数以元组或列表的形式提供：

cursor.execute("INSERT INTO employee (staff_number, fname, lname, gender, joining, birth_date) "
    "VALUES (NULL, ?, ?, ?, ?, ?)",
    ('老三', '张', 'm', '2018-9-1', '1990-1-1'))
print("%d rows inserted." % cursor.rowcount)
    
# 也可以用executemany方法多次执行同一条SQL语句：

cursor.executemany("INSERT INTO employee (staff_number, fname, lname, gender, joining, birth_date) "
    "VALUES (NULL, ?, ?, ?, ?, ?)",
    (('老四', '李', 'm', '2018-9-1', '1990-1-1'),
    ('老五', '王', 'm', '2018-9-1', '1990-1-1'),
    ('老六', '赵', 'm', '2018-9-1', '1990-1-1')))
print("%d rows inserted." % cursor.rowcount)


# 修改李老四的joining字段：

cursor.execute("UPDATE employee SET joining=? WHERE lname=? AND fname=?",
    ('2019-7-1', '李', '老四'))
print("%d rows updated." % cursor.rowcount)
    
# 删除赵老六的记录：

cursor.execute("DELETE FROM employee WHERE lname=? AND fname=?",
    ('赵', '老六'))
print("%d rows deleted." % cursor.rowcount)

# commit使得以上操作生效：
connection.commit()


# 读取数据表内容：

cursor.execute("SELECT * FROM employee") 
print("\nUsing fetchall:")
# 一次读取所有记录：
result = cursor.fetchall() 
for row in result:
    print(row)
    # 注：row是一个元组，因此可以用row[i]表示该记录的第i个字段。


cursor.execute("SELECT * FROM employee") 
print("\nUsing fetch one:")
# 一次读取一条记录： 
while True:  
    row = cursor.fetchone() 
    if not row: # 读取到末尾的时候，fetchone返回None。
        break
    print(row)
    
# 也可以用cursor.fetchmany(n)一次读取多条记录。


# （4）关闭游标。
cursor.close()

# （5）关闭数据库连接。
connection.close()



